package com.ht.pms.core.dao.impl;

import com.ht.pms.core.dao.SalaryUserDao;
import com.ht.pms.core.dto.tempalte.Template;
import com.ht.pms.core.dto.user.DdUser;
import com.ht.pms.core.dto.user.UcOrg;
import com.ht.pms.core.dto.user.UcUser;
import com.ht.pms.core.dto.user.UserInfoDto;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Service
public class SalaryUserDaoImpl implements SalaryUserDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void updateUserInfo(List<UcUser> userList) {
		StringBuilder sb = new StringBuilder();
		sb.append(
				"INSERT INTO t_uc_user (user_id,user_name,org_code,root_org_code,org_path,email,id_no,mobile,create_operator,jpa_version,");
		sb.append("del_flag,update_operator,job_number,data_source,user_type,is_org_user,status) ");
		sb.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ");
		sb.append(" user_id =?, user_name =?, org_code =?, root_org_code =?, org_path =?, email =?, id_no =?, mobile =?, create_operator =?,");
		sb.append(" jpa_version =?, del_flag =?, update_operator =?, job_number =?, data_source =?, user_type =?, is_org_user =?,status=?");

		jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				UcUser ucUser = userList.get(i);
				ps.setString(1, "".equals(ucUser.getUserId())?null:ucUser.getUserId());
				ps.setString(2, ucUser.getUserName());
				ps.setString(3, ucUser.getOrgCode());
				ps.setString(4, ucUser.getRootOrgCode());
				ps.setString(5, ucUser.getOrgPath());
				ps.setString(6, "".equals(ucUser.getEmail())?null:ucUser.getEmail());
				ps.setString(7, ucUser.getIdNo());
				ps.setString(8, "".equals(ucUser.getMobile())?null:ucUser.getMobile());
				ps.setString(9, ucUser.getCreateOperator());
				ps.setInt(10, ucUser.getJpaVersion());
				ps.setInt(11, ucUser.getDelFlag());
				ps.setString(12, ucUser.getUpdateOperator());
				ps.setString(13, "".equals(ucUser.getJobNumber())?null:ucUser.getJobNumber());
				ps.setInt(14, ucUser.getDataSource());
				ps.setString(15, ucUser.getUserType());
				ps.setInt(16, ucUser.getIsOrgUser());
				ps.setString(17, ucUser.getStatus());

				ps.setString(18, "".equals(ucUser.getUserId())?null:ucUser.getUserId());
				ps.setString(19, ucUser.getUserName());
				ps.setString(20, ucUser.getOrgCode());
				ps.setString(21, ucUser.getRootOrgCode());
				ps.setString(22, ucUser.getOrgPath());
				ps.setString(23, "".equals(ucUser.getEmail())?null:ucUser.getEmail());
				ps.setString(24, ucUser.getIdNo());
				ps.setString(25, "".equals(ucUser.getMobile())?null:ucUser.getMobile());
				ps.setString(26, ucUser.getCreateOperator());
				ps.setInt(27, ucUser.getJpaVersion());
				ps.setInt(28, ucUser.getDelFlag());
				ps.setString(29, ucUser.getUpdateOperator());
				ps.setString(30, "".equals(ucUser.getJobNumber())?null:ucUser.getJobNumber());
				ps.setInt(31, ucUser.getDataSource());
				ps.setString(32, ucUser.getUserType());
				ps.setInt(33, ucUser.getIsOrgUser());
				ps.setString(34, ucUser.getStatus());
			}

			@Override
			public int getBatchSize() {
				return userList.size();
			}
		});
	}

	@Override
	public void updateOrgInfo(List<UcOrg> ucOrgList) {
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO t_uc_org (org_code,org_name,org_name_cn,parent_org_code,root_org_code,org_path,org_type,");
		sb.append("data_source,sequence,remark,create_operator,jpa_version,del_flag,update_operator) ");
		sb.append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ");
		sb.append(" org_code =?, org_name =?, org_name_cn =?, parent_org_code =?,root_org_code =?, org_path =?, org_type =?, ");
		sb.append(" data_source =?, sequence =?, remark =?,create_operator =?, jpa_version =?, del_flag =?, update_operator =? ");

		jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				UcOrg ucOrg = ucOrgList.get(i);
				ps.setString(1, ucOrg.getOrgCode());
				ps.setString(2, ucOrg.getOrgName());
				ps.setString(3, ucOrg.getOrgNameCn());
				ps.setString(4, ucOrg.getParentOrgCode());
				ps.setString(5, ucOrg.getRootOrgCode());
				ps.setString(6, ucOrg.getOrgPath());
				ps.setString(7, ucOrg.getOrgType());
				ps.setInt(8, ucOrg.getDataSource());
				ps.setInt(9, ucOrg.getSequence());
				ps.setString(10, ucOrg.getRemark());
				ps.setString(11, ucOrg.getCreateOperator());
				ps.setInt(12, ucOrg.getJpaVersion());
				ps.setInt(13, ucOrg.getDelFlag());
				ps.setString(14, ucOrg.getUpdateOperator());

				ps.setString(15, ucOrg.getOrgCode());
				ps.setString(16, ucOrg.getOrgName());
				ps.setString(17, ucOrg.getOrgNameCn());
				ps.setString(18, ucOrg.getParentOrgCode());
				ps.setString(19, ucOrg.getRootOrgCode());
				ps.setString(20, ucOrg.getOrgPath());
				ps.setString(21, ucOrg.getOrgType());
				ps.setInt(22, ucOrg.getDataSource());
				ps.setInt(23, ucOrg.getSequence());
				ps.setString(24, ucOrg.getRemark());
				ps.setString(25, ucOrg.getCreateOperator());
				ps.setInt(26, ucOrg.getJpaVersion());
				ps.setInt(27, ucOrg.getDelFlag());
				ps.setString(28, ucOrg.getUpdateOperator());

			}

			@Override
			public int getBatchSize() {
				return ucOrgList.size();
			}
		});
	}



	@Override
	public void updateDdUser(List<DdUser> ddUserList) {
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO t_uc_dd_user(user_id,user_name,dept_id,email,id_no,mobile,job_number,position) ");
		sb.append(" VALUES(?,?,?,?,?,?,?,?)  ON DUPLICATE KEY UPDATE ");
		sb.append(" user_id=?,user_name=?,dept_id=?,email=?,id_no=?,mobile=?,job_number=?,position=?");
		jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				DdUser ddUser = ddUserList.get(i);
				ps.setString(1, ddUser.getUserId());
				ps.setString(2, ddUser.getUserName());
				ps.setString(3, ddUser.getDeptId());
				ps.setString(4, ddUser.getEmail());
				ps.setString(5, ddUser.getIdNo());
				ps.setString(6, ddUser.getMobile());
				ps.setString(7, ddUser.getJobNumber());
				ps.setString(8, ddUser.getPosition());

				ps.setString(9, ddUser.getUserId());
				ps.setString(10, ddUser.getUserName());
				ps.setString(11, ddUser.getDeptId());
				ps.setString(12, ddUser.getEmail());
				ps.setString(13, ddUser.getIdNo());
				ps.setString(14, ddUser.getMobile());
				ps.setString(15, ddUser.getJobNumber());
				ps.setString(16, ddUser.getPosition());



			}

			@Override
			public int getBatchSize() {
				return ddUserList.size();
			}
		});
	}

	@Override
	public void saveQueryStartTime(String startTime, String endTime) {
		String sql = "insert into t_scheduled_log(start_time,end_time)VALUES('" + startTime + "','" + endTime + "')";
		jdbcTemplate.update(sql);
	}

	@Override
	public String queryEndTime() {
		String sql = "select t.end_time from t_scheduled_log t ORDER BY id desc LIMIT 0,1";
		String time = null;
		try {
			time = jdbcTemplate.queryForObject(sql, String.class);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return time;
	}

	@Override
	public UserInfoDto queryUserInfoByJobCode(String jobCode) {
		String sql = "select * from t_uc_user where job_number = ?";
		List<UserInfoDto> userInfos = jdbcTemplate.query(sql, new Object[] { jobCode }, new BeanPropertyRowMapper<UserInfoDto>(UserInfoDto.class));
		if(userInfos == null || userInfos.isEmpty()) {
			return null;
		}
		return userInfos.get(0);
	}



}
